package com.scalar.chiptrack.sales.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.List;
import java.util.HashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.io.IOException;

import org.apache.struts.util.LabelValueBean;
import org.apache.struts.util.MessageResources;
import org.apache.poi.ss.usermodel.Workbook;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Labels;
import com.scalar.chiptrack.commons.ExcelColumnProperties;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.sales.SalesPerson;
import com.scalar.chiptrack.sales.ShippingMethod;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.chiptrack.users.User;



public class NewCustomerDAO
 {

   /**
    * Default Constructor
    *
    */
   public NewCustomerDAO()
   {

   }

   //Added by varma 21 Dec 2004 to load all the sales persons
   public static ArrayList<SalesPerson> getAllSalesPersonsWithActions() throws ScalarException
   {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    SalesPerson salesperson = null;
    ArrayList<SalesPerson> salespersonList = new ArrayList<SalesPerson>();


    try {

          conn = DataSource.getConnection();
          pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.customer.salesperson.getallsalespersons") );
          rs = pstmt.executeQuery();

          while(rs.next())
          {
            salesperson = new SalesPerson();
            salesperson.setSalesPersonId(rs.getInt("salesPerson_id") );
            salesperson.setSalesPerson(rs.getString("SalesPerson_Name"));
            salesperson.setDisabled(rs.getInt("Deactivated") == 0 ? true :false);
            salespersonList.add(salesperson);
          }


        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get SalesPersons from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salespersonList;
   }

    public static void insertSalesPerson(DBTransaction dbTransaction, String salesperson, int addressId, float salesPercent, int regionId, boolean isSalesAckMail, boolean isQuoteAckMail, boolean isShipAlerMail) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;


        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.salesperson.insertSalesPerson"));

            if( salesperson != null ){
                salesperson = salesperson.trim() ;
            }
            pstmt.setString (1, salesperson );
            pstmt.setInt    (2, addressId );
            pstmt.setFloat  (3, salesPercent );
            pstmt.setInt    (4, regionId );
            pstmt.setInt    (5, isSalesAckMail ? 0 : 1 );
            pstmt.setInt    (6, isQuoteAckMail ? 0 : 1 );
            pstmt.setInt    (7, isShipAlerMail ? 0 : 1 );
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert salesperson, \"" + salesperson + "\" into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static SalesPerson getSalesPersonDetails(DBTransaction dbtransaction, int salesPersonId ) throws ScalarException
     {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            SalesPerson salesperson = null;
            int addressId = -1;
            try {
                  if ( dbtransaction == null ) {
                        conn = DataSource.getConnection();
                  }
                  else {
                        conn = dbtransaction.getConnection();
                  }
                  pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.customer.salesperson.getSalesPersonDetails") );
                  pstmt.setInt ( 1, salesPersonId );
                  rs = pstmt.executeQuery();

                  while(rs.next()) {
                        salesperson = new SalesPerson();
                        salesperson.setSalesPersonId(rs.getInt("SalesPerson_Id") );
                        salesperson.setSalesPerson  (rs.getString("SalesPerson_Name"));
                        salesperson.setSalesPercent (rs.getFloat("Sales_Percent") );
                        salesperson.setRegionId       (rs.getInt("Region_Id"));
                        addressId = rs.getInt("Address_Id");
                        salesperson.setSalesAckMail( rs.getInt ( "SALES_ACK_MAIL" ) == 0 );
                        salesperson.setQuoteAckMail( rs.getInt ( "QUOTE_ACK_MAIL" ) == 0 );
                        salesperson.setShipAlerts( rs.getInt ( "SHIP_ALERT_MAIL" ) == 0 );
                  }
            }
            catch (Exception sqlex) {
                    sqlex.printStackTrace();
                    throw new ScalarException ("Could not get SalesPersons from database", sqlex);
            }
            finally {
                    if (rs != null) {
                        try { rs.close(); } catch (SQLException excp) {}
                    }
                    if (pstmt != null) {
                        try { pstmt.close(); } catch (SQLException excp) {}
                    }
                    if (conn != null) {
                        try { conn.close(); } catch (SQLException excp) {}
                    }
            }
            if ( addressId > 0 )
            {
                //Address address = CustomerDAO.getAddress( addressId );
                Address address = NewCustomerDAO.loadAddressFromCT( addressId );
                if ( address != null )
                {
                    address.setAddressId( addressId );
                }
                salesperson.setAddress( address );
            }
            return salesperson;
     }


    public static void updateSalesPerson(DBTransaction dbTransaction, int salesPersonId, String salesperson, int addressId, float salesPercent, int regionId, boolean isSalesAckMail, boolean isQuoteAckMail, boolean isShipAlertMail) throws ScalarException
    {
            Connection conn = null;
            PreparedStatement pstmt = null;
            boolean success = false;


            try {
                // Get the connection from regular datasource if its not in a manual transaction
                if (dbTransaction == null) {
                    conn = DataSource.getConnection();
                }
                else {
                    conn = dbTransaction.getConnection();
                }

                pstmt = conn.prepareStatement ( SqlQueryMappings.get("sales.customer.salesperson.updateSalesPerson") );

                if( salesperson != null ){
                    salesperson = salesperson.trim() ;
                }
                pstmt.setString ( 1, salesperson );
                pstmt.setInt    ( 2, addressId );
                pstmt.setFloat  ( 3, salesPercent ) ;
                pstmt.setInt    ( 4, regionId );
                pstmt.setInt    ( 5, isSalesAckMail ? 0 : 1 );
                pstmt.setInt    ( 6, isQuoteAckMail ? 0 : 1 );
                pstmt.setInt    ( 7, isShipAlertMail ? 0 : 1 );
                pstmt.setInt    ( 8, salesPersonId );

                pstmt.executeUpdate();

                success = true;
            }
            catch (Exception sqle) {
                sqle.printStackTrace();
                throw new ScalarException ("Could not Update salesperson, \"" + salesperson + "\" into database", sqle);
            }
            finally {
                if (dbTransaction == null) {
                    try {
                        if (conn != null) {
                            if (success == true)
                                conn.commit();
                            else
                                conn.rollback();
                        }
                    }
                    catch (SQLException ignore) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore){}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore){}
                }
            }
    }


    public static void deactivateSalesPerson( int salesPersonId, int deactivated ) throws ScalarException
    {


           Connection conn = null;
           PreparedStatement pstmt = null;
           boolean success = false;


           try {
               conn = DataSource.getConnection();
               pstmt = conn.prepareStatement ( SqlQueryMappings.get("sales.customer.salesperson.deacivateSalesPerson") );

               pstmt.setInt ( 1, deactivated );
               pstmt.setInt ( 2 , salesPersonId );
               pstmt.executeUpdate();

               success = true;
           }
           catch (Exception sqle) {
               sqle.printStackTrace();
               throw new ScalarException ("Could not deactivate SalesPerson information into database", sqle);
           }
           finally {
               try {
                   if (conn != null) {
                       if (success == true) {
                           conn.commit();
                       }
                       else {
                           conn.rollback();
                       }
                   }
               }
               catch (SQLException ignore) {}

               if (pstmt != null) {
                   try { pstmt.close(); } catch (SQLException ignore){}
               }
               if (conn != null) {
                   try { conn.close(); } catch (SQLException ignore){}
               }
           }
    }

     public static ArrayList<SalesPerson> getAllSalesPersonsList( int customerId ) throws ScalarException
     {

         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         ArrayList<SalesPerson> list = new ArrayList<SalesPerson>();
         SalesPerson salesPerson = null;

         try
         {
             conn = DataSource.getConnection();
             pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.salesperson.getAllSalesPersonsList" ) );
             pstmt.setInt( 1, customerId );

             rs = pstmt.executeQuery();

             while( rs.next() )
             {
                 salesPerson = new SalesPerson( rs.getInt( "SalesPerson_Id" ), rs.getString( "SalesPerson_Name" ), rs.getString( "Flag" ) );
                 list.add( salesPerson );
             }
         }
         catch( Exception sqlex )
         {
             sqlex.printStackTrace();
             throw new ScalarException( "Could not get Sales Person List from database", sqlex );
         }
         finally
         {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
         }

         return list;
     }

     public static boolean deactivateSalesPersonList(String customerId) throws ScalarException
     {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            boolean success = false;

            try
            {
                conn = DataSource.getConnection ();
                pstmt = conn.prepareStatement ( SqlQueryMappings.get("sales.customer.salesperson.deactivateSalesPersonsList") );
                pstmt.setString( 1, customerId );
                pstmt.executeUpdate();

                success = true;
            }
            catch ( Exception sqlex )
            {
                sqlex.printStackTrace ();
                throw new ScalarException ( "Could not  deactivate Sales Person List from database", sqlex );
            }
            finally
            {
                if (conn != null) {
                    try{
                      if (success == true)
                        conn.commit();
                    else
                        conn.rollback();
                    }catch(SQLException sqle){
                        sqle.printStackTrace ();
                        throw new ScalarException ( "Could not Commit Sales Person List into database", sqle );
                    }
                }

                if ( rs != null )
                {
                    try
                    {
                        rs.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
            }

            return success;
      }

    public static boolean saveSalesPersonList(String customerId, String[] salesPersonId) throws ScalarException
    {

            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            boolean success = false;

            try
            {
                conn = DataSource.getConnection ();
                pstmt = conn.prepareStatement ( SqlQueryMappings.get("sales.customer.salesperson.saveSalesPersonsList") );

                if(null != salesPersonId)
                {
                    for(int i=0; i<salesPersonId.length; i++)
                    {
                        pstmt.setString( 1, customerId );
                        pstmt.setString( 2, salesPersonId[i] );

                        pstmt.addBatch();
                    }

                    pstmt.executeBatch();

                }else{
                    success = true;
                }

                success = true;
            }
            catch ( Exception sqlex )
            {
                sqlex.printStackTrace ();
                throw new ScalarException ( "Could not save Sales Person List from database", sqlex );
            }
            finally
            {
                if (conn != null) {
                    try{
                      if (success == true)
                        conn.commit();
                    else
                        conn.rollback();
                    }catch(SQLException sqle){
                        sqle.printStackTrace ();
                        throw new ScalarException ( "Could not Commit Sales Person List into database", sqle );
                    }
                }

                if ( rs != null )
                {
                    try
                    {
                        rs.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
            }

            return success;
    }

    //Added by varma 27 Dec 2004 to load the Sales Persons for the Customer and ODM
    public static ArrayList<LabelValueBean> loadAllSalesPersonsforCustomerAndOdm( int customerId, int odmId ) throws ScalarException
    {

        ArrayList<LabelValueBean> list = new ArrayList<LabelValueBean>();
        Iterator<SalesPerson> salesPersonsList = NewCustomerDAO.getAllSalesPersonsList( customerId ) .iterator();
        Iterator<SalesPerson> odmSalesPersonsList = NewCustomerDAO.getAllSalesPersonsList( odmId ) .iterator();
        try
        {
           list.add( new LabelValueBean("----For Customer----", "-1" ));
           while ( salesPersonsList.hasNext() ) {
                SalesPerson salesPerson = (SalesPerson) salesPersonsList.next();
                if ( "checked".equalsIgnoreCase( salesPerson.getFlag() ) ) {
                    list.add( new LabelValueBean(salesPerson.getSalesPerson(), salesPerson.getSalesPerson() ));
                }
            }
            String endCustomerLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endCustomer" );
            list.add( new LabelValueBean("----For "+endCustomerLabel+"----", "-2" ));
            while ( odmSalesPersonsList.hasNext() ) {
                SalesPerson salesPerson = (SalesPerson) odmSalesPersonsList.next();
                if ( "checked".equalsIgnoreCase( salesPerson.getFlag() ) ) {
                    list.add( new LabelValueBean(salesPerson.getSalesPerson(), salesPerson.getSalesPerson() ));
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        return list;
    }
  //Added by varma on 29 dec 2004 to get the all Rep offices from Sales Order table
   public static ArrayList<String> getAllRepOfficesFromSalesOrder() throws ScalarException
   {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    ArrayList<String> repOfficesList = new ArrayList<String>();


        try {

              conn = DataSource.getConnection();
              pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesorder.getallregionalrepsfromsalesorder") );

              rs = pstmt.executeQuery();

              while(rs.next())
              {
                    repOfficesList.add( rs.getString("Regional_Rep"));
              }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Rep Offices from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    return repOfficesList;
   }
    public static ArrayList<LabelValueBean> getAllRepOfficesFromSalesOrderList() throws ScalarException
   {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    ArrayList<LabelValueBean> repOfficesList = new ArrayList<LabelValueBean>();


        try {

              conn = DataSource.getConnection();
              pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesorder.getallregionalrepsfromsalesorder") );

              rs = pstmt.executeQuery();

              while(rs.next())
              {
                    repOfficesList.add( new LabelValueBean(rs.getString("Regional_Rep"), rs.getString("Regional_Rep") ) );
              }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Rep Offices from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    return repOfficesList;
   }

     public static ArrayList<SalesPerson> loadAllSalesPersonsWithRegions() throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;

         ArrayList<SalesPerson> salesPersonsList = new ArrayList<SalesPerson>();
         SalesPerson salesPerson = null;
         try
         {
             conn = DataSource.getConnection();
             pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.salespersonregions" ) );

             rs = pstmt.executeQuery();

             while( rs.next() )
             {
                 salesPerson = new SalesPerson();
                 salesPerson.setSalesPerson( rs.getString( "SalesPerson_Name" ) );
                 salesPerson.setRegion( rs.getString( "Region_Name" ) );
                 salesPersonsList.add( salesPerson );
             }
         }
         catch( Exception sqlex )
         {
             sqlex.printStackTrace();
             throw new ScalarException( "Could not get Rep Offices from database", sqlex );
         }
         finally
         {
             if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
         }

        return salesPersonsList;
    }

    public static ArrayList<LabelValueBean> getAllCustomerNamesOfTypeCustomer() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LabelValueBean> customers = new ArrayList<LabelValueBean>();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select C.Customer_ID, C_Name from Customer C, Customer_Type_Relationship CTR where C_Disabled = 1 and C.Customer_ID = CTR.Customer_ID and CTR.C_Type_ID = 1 ORDER BY C_Name");
            rs = pstmt.executeQuery();
            customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            while(rs.next())
            {
                int customerId = rs.getInt("Customer_ID");
                customers.add(new org.apache.struts.util.LabelValueBean(rs.getString("C_Name"),Integer.toString(customerId)));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customers from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
   }


    public static void updateRegion( DBTransaction dbTransaction, String oldRegion, String newRegion ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("sales.customer.updateRegionName") );

            if( newRegion != null ){
                newRegion = newRegion.trim() ;
            }
            pstmt.setString ( 1, newRegion );
            pstmt.setString ( 2, oldRegion ) ;

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update salesperson, \"" + newRegion + "\" into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    public static ArrayList<LabelValueBean> loadAllOdms() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LabelValueBean> Odms = new ArrayList<LabelValueBean> ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( "select C_Name from Customer C, Customer_Type_Relationship CTR where C_Disabled = 1 and C.Customer_ID = CTR.Customer_ID and CTR.C_Type_ID = 2 ORDER BY C_Name" );

            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                String odm_name = rs.getString ( "C_Name" );
                Odms.add ( new org.apache.struts.util.LabelValueBean ( odm_name, odm_name ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End Users from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return Odms;
    }

    /**
     * To Save Address into Chiptrac Data Base, even Integration Type is Great Plains..
     * @param dbTransaction
     * @param address
     * @return
     * @throws ScalarException
     */
    public static int saveAddressIntoCT(DBTransaction dbTransaction, Address address ) throws ScalarException
    {
        int addressId = -1;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean insert = false;
        boolean success = false;

        try {

            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
            else
            {
                conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }

            if ( address != null )
            {
                addressId = address.getAddressId();
                if ( address.getAddressId() == -1 )
                {
                    insert = true;
                }

                if(insert)
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertAddress"));
                } else {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.updateAddress"));
                }

                pstmt.setString (1,  (address.getAddress1()         != null ) ? address.getAddress1().trim()        : "" );
                pstmt.setString (2,  (address.getAddress2()         != null ) ? address.getAddress2().trim()        : "" );
                pstmt.setString (3,  (address.getCity()             != null ) ? address.getCity().trim()            : "" );
                pstmt.setString (4,  (address.getState()            != null ) ? address.getState().trim()           : "" );
                pstmt.setString (5,  (address.getZipCode()          != null ) ? address.getZipCode().trim()         : "" );
                pstmt.setString (6,  (address.getCountry()          != null ) ? address.getCountry().trim()         : "" );
                pstmt.setString (7,  (address.getPhoneNumber()      != null ) ? address.getPhoneNumber().trim()     : "" );
                pstmt.setString (8,  (address.getFaxNumber()        != null ) ? address.getFaxNumber().trim()       : "" );
                pstmt.setString (9,  (address.getEmail()            != null ) ? address.getEmail().trim()           : "" );
                pstmt.setString (10, (address.getContactPerson()    != null ) ? address.getContactPerson().trim()   : "" );
                pstmt.setString (11, (address.getCellNumber()       != null ) ? address.getCellNumber().trim()      : "" );
                pstmt.setString (12, (address.getAddress3()         != null ) ? address.getAddress3().trim()        : "" );
                pstmt.setString (13, (address.getAddress4()         != null ) ? address.getAddress4().trim()        : "" );
                pstmt.setString (14, (address.getEpicoreShipCode()  != null ) ? address.getEpicoreShipCode().trim() : "" );
                pstmt.setString (15, (address.getTagName()          != null ) ? address.getTagName().trim() : "" );

                if(!insert)
                {
                    pstmt.setInt (16, addressId);
                    pstmt.executeUpdate();
                }
                else
                {
                    pstmt.execute();
                    pstmt = conn.prepareStatement("SELECT @@IDENTITY as address_id");
                    rs = pstmt.executeQuery();

                    if (rs.next())
                    {
                        addressId = rs.getInt("address_id");
                    }
                }

                success = true;

            }
        }
        catch(SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Save Address into Chiptrac Database", sqle);
        }
        finally{
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return addressId;
    }


    /**
     * To Fetch Address From Chiptrac Data Base, even Integration Type is Great Plains.
     * @param addressId
     * @return
     * @throws ScalarException
     */
    public static Address loadAddressFromCT( int addressId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Address address = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.getAddress"));
            pstmt.setInt (1, addressId);

            rs = pstmt.executeQuery();

            if (rs.next() == false) {

            }
            else {
                address = new Address(rs.getInt("Address_ID"),
                                      rs.getString("Address_1"),
                                      rs.getString("Address_2"),
                                      rs.getString("City"),
                                      rs.getString("State"),
                                      rs.getString("ZipCode"),
                                      rs.getString("Country"),
                                      rs.getString("Phone_Number"),
                                      rs.getString("Fax_Number"),
                                      rs.getString("Email"),
                                      rs.getString("Contact_Person"),
                                      rs.getString("Cell_Number"));
                address.setAddress3(rs.getString("Address_3"));
                address.setAddress4(rs.getString("Address_4"));
                address.setEpicoreShipCode( rs.getString( "Epicore_Ship_Code" ) );
                address.setTagName( rs.getString( "TAG_NAME" ) );

            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Address from Chip Trac Database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return address;
    }

    public static ArrayList<LabelValueBean> getSalesPersonList( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList<LabelValueBean> salesPersonList = new ArrayList<LabelValueBean>();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "select distinct SalesPerson_Id,SalesPerson_Name from sales_persons where Deactivated <> 1 order by SalesPerson_Name" );
            rset = pstmt.executeQuery();
            while (rset.next())
            {
                String salesPerson_Id = rset.getString("SalesPerson_Id");
                String salesPerson_Name = rset.getString("SalesPerson_Name");

                salesPersonList.add(new org.apache.struts.util.LabelValueBean(salesPerson_Name, salesPerson_Id));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Sales Persons from database", sqlex);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return salesPersonList;
    }


    /**
      *
      * @param salesOrderType
      * 0 - sample sales orders
      * 1 - Regular Sales Orders
      * @param isSalesOrderTypeConsidered
      * true  - only the salesOrderType end Users are included
      * false - both SO end Users are included (salesOrderType is ignored)
      * @return
      * @throws ScalarException
      */
     public static ArrayList<LabelValueBean> loadAllEndUsers(int salesOrderType, boolean isSalesOrderTypeConsidered) throws ScalarException
        {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            ArrayList<LabelValueBean> allEndUsers = new ArrayList<LabelValueBean> ();

            try
            {
                conn = DataSource.getConnection ();
                if ( isSalesOrderTypeConsidered )
                {
                    pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 3) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL AND SO.SO_Is_Sample = ?) ORDER BY C_NAME" );
                    pstmt.setInt ( 1, salesOrderType );
                }
                else
                {
                    pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 3) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL) ORDER BY C_NAME" );
                }

                rs = pstmt.executeQuery ();
                while ( rs.next () )
                {
                    String endUser_name = rs.getString ( "C_Name" );
                    allEndUsers.add ( new org.apache.struts.util.LabelValueBean ( endUser_name, endUser_name ) );
                }
            }
            catch ( Exception sqlex )
            {
                sqlex.printStackTrace ();
                throw new ScalarException ( "Could not get End Users from database", sqlex );
            }
            finally
            {
                if ( rs != null )
                {
                    try
                    {
                        rs.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
            }
            return allEndUsers;
        }

    /**
      *
      * @param salesOrderType
      * 0 - sample sales orders
      * 1 - Regular Sales Orders
      * @param isSalesOrderTypeConsidered
      * true  - only the salesOrderType end Customers are included
      * false - both SO end customers are included (salesOrderType is ignored)
      * @return
      * @throws ScalarException
      */
    public static ArrayList<LabelValueBean> loadAllEndCustomersWithSOEndcustomers( int salesOrderType, boolean isSalesOrderTypeConsidered ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LabelValueBean> allEndCustomers = new ArrayList<LabelValueBean> ();

        try
        {
            conn = DataSource.getConnection ();
            if ( isSalesOrderTypeConsidered )
            {
                pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 2) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_Is_Sample = ?) ORDER BY C_NAME" );
                pstmt.setInt( 1, salesOrderType );
            }
            else
            {
                pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 2) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) ) ORDER BY C_NAME" );
            }

            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                String endCustomerName = rs.getString ( "C_Name" );
                allEndCustomers.add ( new org.apache.struts.util.LabelValueBean ( endCustomerName, endCustomerName ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End Customers from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return allEndCustomers;
    }

    public static ArrayList<LabelValueBean> getAllCustomerNamesByCustomerType( String customerType) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LabelValueBean> customers = new ArrayList<LabelValueBean>();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select C.Customer_ID, C_Name from Customer C, Customer_Type_Relationship CTR,CUSTOMER_TYPES CT where C_Disabled = 1 and C.Customer_ID = CTR.Customer_ID\tand CTR.C_TYPE_ID = CT.C_TYPE_ID and ct.C_TYPE_NAME = ? ORDER BY C_Name");
            pstmt.setString ( 1, customerType );
            rs = pstmt.executeQuery();
            //customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            while(rs.next())
            {
                int customerId = rs.getInt("Customer_ID");
                customers.add(new org.apache.struts.util.LabelValueBean(rs.getString("C_Name"),Integer.toString(customerId)));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customers from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
   }

   public static ArrayList<LabelValueBean> getAllCustomerNamesByCustomerTypeId( int customerTypeId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LabelValueBean> customers = new ArrayList<LabelValueBean>();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select C.Customer_ID, C_Name from Customer C, Customer_Type_Relationship CTR,CUSTOMER_TYPES CT where C_Disabled = 1 and C.Customer_ID = CTR.Customer_ID and CTR.C_TYPE_ID = CT.C_TYPE_ID and ct.C_TYPE_ID = ? ORDER BY C_Name");
            pstmt.setInt ( 1, customerTypeId );
            rs = pstmt.executeQuery();
            //customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            while(rs.next())
            {
                int customerId = rs.getInt("Customer_ID");
                customers.add(new org.apache.struts.util.LabelValueBean(rs.getString("C_Name"),Integer.toString(customerId)));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customers from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
   }

    public static ArrayList<LabelValueBean> loadCustomerType() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LabelValueBean> customersTypes = new ArrayList<LabelValueBean>();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT DISTINCT C_TYPE_NAME,C_TYPE_ID FROM CUSTOMER_TYPES");
            rs = pstmt.executeQuery();
            //customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            while(rs.next())
            {
                //int customerId = rs.getInt("Customer_ID");
                customersTypes.add(new org.apache.struts.util.LabelValueBean(rs.getString("C_TYPE_NAME"),rs.getString("C_TYPE_ID")));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get CustomersTypes from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customersTypes;
   }

   public static ArrayList getCustomerDetails ( DBTransaction dbTransaction, String customerType, String customer, String region, String status, String defaultPrompt ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList lotInfoList   = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL sp_customer_report_proc( ?, ?, ?, ?, ? ) }" );
            pstmt.setString( 1, customerType );
            pstmt.setString( 2, customer );
            pstmt.setString( 3, region );
            pstmt.setString( 4, status );
            pstmt.setString( 5, defaultPrompt );

            rset    = pstmt.executeQuery();

            lotInfoList = ExcelUtils.getAllResultSetRows(rset, true );
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Customer records", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return lotInfoList;
    }

 public static ArrayList loadBacklogReprot ( DBTransaction dbTransaction, String productId,String soNumber,String customerId,String salesPerson,String asofdate,String backlogType,int orderType,String category,String defaultPrompt,String byPeriod,String region, String reportType, String endUser) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList BacklogList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_backlog_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         pstmt.setString( 1, productId );
         pstmt.setString( 2, soNumber );
         pstmt.setString( 3, customerId );
         pstmt.setString( 4, salesPerson );
         pstmt.setString( 5, asofdate );
         pstmt.setString( 6, backlogType );
         pstmt.setInt( 7, orderType );
         pstmt.setString( 8, category );
         pstmt.setString( 9, defaultPrompt );
         pstmt.setString( 10, byPeriod );
         pstmt.setString( 11, reportType );
         pstmt.setString( 12, region );
         pstmt.setString( 13, endUser );

         rset    = pstmt.executeQuery();
         BacklogList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Backlog records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return BacklogList;
 }

 public static ArrayList loadBacklogJobReprot ( DBTransaction dbTransaction, String productId,String soNumber,String customerId,String salesPerson,String asofdate,String backlogType,int orderType,String category,String defaultPrompt,String byPeriod,String reportType,String region, String endUser) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList BacklogList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_backlog_report_job_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,? ) }" );
         pstmt.setString( 1, productId );
         pstmt.setString( 2, soNumber );
         pstmt.setString( 3, customerId );
         pstmt.setString( 4, salesPerson );
         pstmt.setString( 5, asofdate );
         pstmt.setString( 6, backlogType );
         pstmt.setInt( 7, orderType );
         pstmt.setString( 8, category );
         pstmt.setString( 9, defaultPrompt );
         pstmt.setString( 10, byPeriod );
         pstmt.setString( 11, reportType );
         pstmt.setString( 12, region );
         pstmt.setString( 13, endUser );

         rset    = pstmt.executeQuery();
         BacklogList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Backlog records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return BacklogList;
 }
 public static Workbook loadBacklogReprot ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
 {
        ArrayList backLogList = loadBacklogReprot ( dbTransaction,  (String)paramMap.get("productId"),
                                                                                        (String)paramMap.get("soNumber"),
                                                                                        (String)paramMap.get("customerId"),
                                                                                        (String)paramMap.get("salesPerson"),
                                                                                        (String)paramMap.get("asOfDate"),
                                                                                        (String)paramMap.get("backlogType"),
                                                                                        Integer.parseInt((String)paramMap.get("orderType") ),
                                                                                        (String)paramMap.get("category"),
                                                                                        (String)paramMap.get("defaultPrompt"),
                                                                                        (String)paramMap.get("byPeriod"),
                                                                                        (String)paramMap.get("region"),
                                                                                        (String)paramMap.get("reportType"),
                                                                                        (String)paramMap.get("endUser") );

        ExcelColumnProperties colProps1 = new ExcelColumnProperties("EXTENDED PRICE");
        colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps1.setShowTotal(true);
        ExcelColumnProperties colProps2 = new ExcelColumnProperties("UNIT COST");
        colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);

        ExcelColumnProperties colProps3 = new ExcelColumnProperties("TOTAL REBATE AMOUNT");
        colProps3.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps3.setShowTotal(true);
        ExcelColumnProperties colProps4 = new ExcelColumnProperties("NET POST REBATE");
        colProps4.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps4.setShowTotal(true);
        ExcelColumnProperties colProps5 = new ExcelColumnProperties("REBATE UNIT AMOUNT");
        colProps5.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);

        List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
        list.add(colProps1);list.add(colProps2);
        list.add(colProps3);list.add(colProps4);list.add(colProps5);
        return DataDumpReportsHelper.getWorkbook( backLogList , "Backlog Report", null,null, list );
  }
  public static Workbook loadSalesBookingReprot ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
 {
        ArrayList bookingsList = loadSalesBookingReprot ( dbTransaction,  (String)paramMap.get("fromdate"),
                                                                                        (String)paramMap.get("todate"),
                                                                                        (String)paramMap.get("customers"),
                                                                                        (String)paramMap.get("products"),
                                                                                        (String)paramMap.get("region"),
                                                                                        (String)paramMap.get("repoffices"),
                                                                                        (String)paramMap.get("endCustomer"),
                                                                                        (String)paramMap.get("sperson"),
                                                                                        (String)paramMap.get("defaultPrompt"),
                                                                                        (String)paramMap.get("bookingType"),
                                                                                        Integer.parseInt((String)paramMap.get("orderType") ),
                                                                                        (String)paramMap.get("endUser"),
                                                                                        (String)paramMap.get("period"),
                                                                                        (String)paramMap.get("reportType") );

        ExcelColumnProperties colProps1 = new ExcelColumnProperties("EXTENDED PRICE");
        colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps1.setShowTotal(true);
        ExcelColumnProperties colProps2 = new ExcelColumnProperties("UNIT PRICE");
        colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        ExcelColumnProperties colProps3 = new ExcelColumnProperties("TOTAL REBATE AMOUNT");
        colProps3.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps3.setShowTotal(true);
        ExcelColumnProperties colProps4 = new ExcelColumnProperties("NET POST REBATE");
        colProps4.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps4.setShowTotal(true);
        ExcelColumnProperties colProps5 = new ExcelColumnProperties("REBATE UNIT AMOUNT");
        colProps5.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);

        List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
        list.add(colProps1);list.add(colProps2);
        list.add(colProps3);list.add(colProps4);list.add(colProps5);
        return DataDumpReportsHelper.getWorkbook( bookingsList , "Sales Booking Report", (String)paramMap.get("fromdate"), (String)paramMap.get("todate"), list );
  }
  public static Workbook loadBillingReprot ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException,IOException
 {
        ArrayList billingList = loadBillingReprot ( dbTransaction,  (String)paramMap.get("product"),
                                                                                        (String)paramMap.get("salesorderno"),
                                                                                        (String)paramMap.get("customer"),
                                                                                        (String)paramMap.get("salesPerson"),
                                                                                        (String)paramMap.get("datefrom"),
                                                                                        (String)paramMap.get("dateto"),
                                                                                        Integer.parseInt((String)paramMap.get("orderType") ),
                                                                                        (String)paramMap.get("period"),
                                                                                        (String)paramMap.get("reportType"),
                                                                                        (String)paramMap.get("region"),
                                                                                        (String)paramMap.get("defaultPrompt"),
                                                                                         (String)paramMap.get("endUser")       );

        ExcelColumnProperties colProps1 = new ExcelColumnProperties("EXTENDED PRICE");
        colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps1.setShowTotal(true);
        ExcelColumnProperties colProps2 = new ExcelColumnProperties("UNIT PRICE");
        colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        ExcelColumnProperties colProps3 = new ExcelColumnProperties("TOTAL REBATE AMOUNT");
        colProps3.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps3.setShowTotal(true);
        ExcelColumnProperties colProps4 = new ExcelColumnProperties("NET POST REBATE");
        colProps4.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps4.setShowTotal(true);
        ExcelColumnProperties colProps5 = new ExcelColumnProperties("REBATE UNIT AMOUNT");
        colProps5.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);

        List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
        list.add(colProps1);list.add(colProps2);
        list.add(colProps3);list.add(colProps4);list.add(colProps5);

        return DataDumpReportsHelper.getWorkbook( billingList , "Billing Report", (String)paramMap.get("datefrom"), (String)paramMap.get("dateto"), list );
  }
 public static Workbook loadBacklogReprotJob ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
 {
        ArrayList backLogList = loadBacklogJobReprot ( dbTransaction,  (String)paramMap.get("productId"),
                                                                                        (String)paramMap.get("soNumber"),
                                                                                        (String)paramMap.get("customerId"),
                                                                                        (String)paramMap.get("salesPerson"),
                                                                                        (String)paramMap.get("asOfDate"),
                                                                                        (String)paramMap.get("backlogType"),
                                                                                        Integer.parseInt((String)paramMap.get("orderType") ),
                                                                                        (String)paramMap.get("category"),
                                                                                        (String)paramMap.get("defaultPrompt"),
                                                                                        (String)paramMap.get("byPeriod"),
                                                                                        (String)paramMap.get("reportType"),
                                                                                        (String)paramMap.get("region"),
                                                                                        (String)paramMap.get("endUser") );

        ExcelColumnProperties colProps1 = new ExcelColumnProperties("EXTENDED PRICE");
        colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps1.setShowTotal(true);
        ExcelColumnProperties colProps2 = new ExcelColumnProperties("UNIT COST");
        colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
        list.add(colProps1);
        list.add(colProps2);
        return DataDumpReportsHelper.getWorkbook( backLogList , "Backlog Report",null, null, list );
  }
  public static Workbook loadBBBReprot ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
 {
        ArrayList bbbList = loadBBBReprotList ( dbTransaction,  paramMap );
        ExcelColumnProperties colProps1 = new ExcelColumnProperties("BACKLOG_AMOUNT");
        colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps1.setShowTotal(true);
        ExcelColumnProperties colProps2 = new ExcelColumnProperties("UNIT PRICE");
        colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        ExcelColumnProperties colProps3 = new ExcelColumnProperties("BOOKING_AMOUNT");
        colProps3.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps3.setShowTotal(true);
        ExcelColumnProperties colProps4 = new ExcelColumnProperties("SHIPPING_AMOUNT");
        colProps4.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
        colProps4.setShowTotal(true);

        List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
        list.add(colProps1);list.add(colProps2);
        list.add(colProps3);list.add(colProps4);
        return DataDumpReportsHelper.getWorkbook( bbbList , "Sales BBB Report", (String)paramMap.get("fromDate"), (String)paramMap.get("toDate"), list );
  }
  //fromdate,todate,customers,products,region,repoffices,enduser,sperson,defaultPrompt,booking,orderType
 public static ArrayList loadSalesBookingReprot ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String endCustomer,String sperson,String defaultPrompt, String bookingType,int orderType,String endUser,String period,String reportType) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList BookingList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_booking_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
         pstmt.setString( 1, fromdate );
         pstmt.setString( 2, todate );
         pstmt.setString( 3, customers );
         pstmt.setString( 4, products );
         pstmt.setString( 5, region );
         pstmt.setString( 6, repoffices );
         pstmt.setString( 7, endCustomer );
         pstmt.setString( 8, sperson );
         pstmt.setString( 9, defaultPrompt );
         pstmt.setString( 10, bookingType );
         pstmt.setInt( 11, orderType );
         pstmt.setString( 12, endUser );
         pstmt.setString( 13, period );
         pstmt.setString( 14, reportType );

         rset    = pstmt.executeQuery();

         BookingList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Booking records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return BookingList;
 }

 public static Workbook loadCustomerARReprot ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
 {
     ArrayList customerARList = loadCustomerARReprot( dbTransaction,  (String)paramMap.get("customerId") );

     ArrayList<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
     
     list.add(new ExcelColumnProperties("CREDIT_LIMIT",ExcelColumnProperties.CELL_STYLE_CURRENCY_$, false ) );
     list.add(new ExcelColumnProperties("AR",ExcelColumnProperties.CELL_STYLE_CURRENCY_$, false ) );
     list.add(new ExcelColumnProperties("SHIP_SCHE_7DAYS",ExcelColumnProperties.CELL_STYLE_CURRENCY_$, false ) );
     list.add(new ExcelColumnProperties("SHIP_SCHE_30DAYS",ExcelColumnProperties.CELL_STYLE_CURRENCY_$, false ) );
     list.add(new ExcelColumnProperties("TOTAL_BACKLOG",ExcelColumnProperties.CELL_STYLE_CURRENCY_$, false ) );
     list.add(new ExcelColumnProperties("HOLD_BACKLOG",ExcelColumnProperties.CELL_STYLE_CURRENCY_$, false ) );
     return DataDumpReportsHelper.getWorkbook( customerARList , "Customer AR Report",null, null, list );
 }
 public static ArrayList  loadCustomerARReprot( DBTransaction dbTransaction, String customerId) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList customerARList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL SP_CUSTOMER_AR_REPORT( ? ) }" );
         pstmt.setString( 1, customerId );
         rset    = pstmt.executeQuery();
         customerARList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load customer AR records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return customerARList;
}

public static String loadSalesBookingReportCSV ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String enduser,String sperson,String defaultPrompt, String bookingType,int orderType,String endCustomer,String period,String reportType) throws ScalarException
{
    Connection conn         = null;
    PreparedStatement pstmt = null;
    ResultSet rset          = null;
    String csvString        = null;

    try
    {

        if ( dbTransaction != null )
        {
            conn    = dbTransaction.getConnection();
        }
        else
        {
            conn    = DataSource.getConnection();
        }

        pstmt   = conn.prepareStatement ( "{ CALL sp_booking_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
        pstmt.setString( 1, fromdate );
        pstmt.setString( 2, todate );
        pstmt.setString( 3, customers );
        pstmt.setString( 4, products );
        pstmt.setString( 5, region );
        pstmt.setString( 6, repoffices );
        pstmt.setString( 7, enduser );
        pstmt.setString( 8, sperson );
        pstmt.setString( 9, defaultPrompt );
        pstmt.setString( 10, bookingType );
        pstmt.setInt( 11, orderType );
        pstmt.setString( 12, endCustomer );
        pstmt.setString( 13, period );
        pstmt.setString( 14, reportType );

        rset    = pstmt.executeQuery();
        csvString = ExcelUtils.getCSVString( rset, true );
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Booking records", e );
     }
     finally
     {
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return csvString;
 }

public static ArrayList loadSalesDeliquentReprot ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String enduser,String sperson,String defaultPrompt, String bookingType,int orderType,String endCustomer,String period,String reportType) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList deliquentList   = new ArrayList();
     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_missed_orders_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
         pstmt.setString( 1, fromdate );
         pstmt.setString( 2, todate );
         pstmt.setString( 3, customers );
         pstmt.setString( 4, products );
         pstmt.setString( 5, region );
         pstmt.setString( 6, repoffices );
         pstmt.setString( 7, enduser );
         pstmt.setString( 8, sperson );
         pstmt.setString( 9, defaultPrompt );
         pstmt.setString( 10, bookingType );
         pstmt.setInt( 11, orderType );
         pstmt.setString( 12, endCustomer );
         pstmt.setString( 13, period );
         pstmt.setString( 14, reportType );

         rset    = pstmt.executeQuery();

         deliquentList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Deliquent Orders", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return deliquentList;
 }
 public static String loadSalesDeliquentReportCSV ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String enduser,String sperson,String defaultPrompt, String bookingType,int orderType,String endCustomer,String period,String reportType) throws ScalarException
  {
      Connection conn         = null;
      PreparedStatement pstmt = null;
      ResultSet rset          = null;
      String csvString        = null;

      try
      {
          if ( dbTransaction != null )
          {
              conn    = dbTransaction.getConnection();
          }
          else
          {
              conn    = DataSource.getConnection();
          }

          pstmt   = conn.prepareStatement ( "{ CALL sp_missed_orders_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
          pstmt.setString( 1, fromdate );
          pstmt.setString( 2, todate );
          pstmt.setString( 3, customers );
          pstmt.setString( 4, products );
          pstmt.setString( 5, region );
          pstmt.setString( 6, repoffices );
          pstmt.setString( 7, enduser );
          pstmt.setString( 8, sperson );
          pstmt.setString( 9, defaultPrompt );
          pstmt.setString( 10, bookingType );
          pstmt.setInt( 11, orderType );
          pstmt.setString( 12, endCustomer );
          pstmt.setString( 13, period );
          pstmt.setString( 14, reportType );

          rset    = pstmt.executeQuery();
          csvString = ExcelUtils.getCSVString( rset, true );
      }
      catch ( Exception e )
      {
          e.printStackTrace();
          throw new ScalarException ( "Could not load Deliquent Orders", e );
      }
      finally
      {
          if ( rset != null )
          {
              try { rset.close(); }catch ( SQLException ignore ) {}
          }
          if ( pstmt != null )
          {
              try { pstmt.close(); } catch ( SQLException ignore ) {}
          }
          if ( conn != null )
          {
              try { conn.close(); } catch ( SQLException ignore ) {}
          }
      }
      return csvString;
  }


public static ArrayList loadSalesCancellationReprot ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String enduser,String sperson,String defaultPrompt, String bookingType,int orderType,String endCustomer,String period,String reportType) throws ScalarException
{
  Connection conn         = null;
  PreparedStatement pstmt = null;
  ResultSet rset          = null;
  boolean success         = false;

  ArrayList CancelList   = new ArrayList();

  try
  {
      if ( dbTransaction != null )
      {
          conn    = dbTransaction.getConnection();
      }
      else
      {
          conn    = DataSource.getConnection();
      }

      pstmt   = conn.prepareStatement ( "{ CALL sp_cancellation_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
      pstmt.setString( 1, fromdate );
      pstmt.setString( 2, todate );
      pstmt.setString( 3, customers );
      pstmt.setString( 4, products );
      pstmt.setString( 5, region );
      pstmt.setString( 6, repoffices );
      pstmt.setString( 7, enduser );
      pstmt.setString( 8, sperson );
      pstmt.setString( 9, defaultPrompt );
      pstmt.setString( 10, bookingType );
      pstmt.setInt( 11, orderType );
      pstmt.setString( 12, endCustomer );
      pstmt.setString( 13, period );
      pstmt.setString( 14, reportType );

      rset    = pstmt.executeQuery();

      CancelList = ExcelUtils.getAllResultSetRows(rset, true);
      success = true;
  }
  catch ( Exception e )
  {
      e.printStackTrace();
      throw new ScalarException ( "Could not load Cancel Orders", e );
  }
  finally
  {
      if ( dbTransaction == null )
      {
          if ( conn != null )
          {
              try
              {
                  if ( success )
                  {
                      conn.commit();
                  }
                  else
                  {
                      conn.rollback();
                  }
              }catch ( SQLException e ){}
          }
      }
      if ( rset != null )
      {
          try { rset.close(); }catch ( SQLException ignore ) {}
      }
      if ( pstmt != null )
      {
          try { pstmt.close(); } catch ( SQLException ignore ) {}
      }
      if ( conn != null )
      {
          try { conn.close(); } catch ( SQLException ignore ) {}
      }
  }
  return CancelList;
}
public static String loadSalesCancellationReportCSV ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String enduser,String sperson,String defaultPrompt, String bookingType,int orderType,String endCustomer,String period,String reportType) throws ScalarException
{
    Connection conn         = null;
    PreparedStatement pstmt = null;
    ResultSet rset          = null;
    String csvString        = null;
    try
    {
       if ( dbTransaction != null )
       {
           conn    = dbTransaction.getConnection();
       }
       else
       {
           conn    = DataSource.getConnection();
       }

       pstmt   = conn.prepareStatement ( "{ CALL sp_cancellation_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
       pstmt.setString( 1, fromdate );
       pstmt.setString( 2, todate );
       pstmt.setString( 3, customers );
       pstmt.setString( 4, products );
       pstmt.setString( 5, region );
       pstmt.setString( 6, repoffices );
       pstmt.setString( 7, enduser );
       pstmt.setString( 8, sperson );
       pstmt.setString( 9, defaultPrompt );
       pstmt.setString( 10, bookingType );
       pstmt.setInt( 11, orderType );
       pstmt.setString( 12, endCustomer );
       pstmt.setString( 13, period );
       pstmt.setString( 14, reportType );

       rset    = pstmt.executeQuery();
       csvString = ExcelUtils.getCSVString( rset, true );
   }
   catch ( Exception e )
   {
       e.printStackTrace();
       throw new ScalarException ( "Could not load Cancel Orders", e );
   }
   finally
   {
       if ( rset != null )
       {
           try { rset.close(); }catch ( SQLException ignore ) {}
       }
       if ( pstmt != null )
       {
           try { pstmt.close(); } catch ( SQLException ignore ) {}
       }
       if ( conn != null )
       {
           try { conn.close(); } catch ( SQLException ignore ) {}
       }
   }
   return csvString;
 }

public static ArrayList loadSalesHoldReprot ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String enduser,String sperson,String defaultPrompt, String bookingType,int orderType,String endCustomer,String period,String reportType) throws ScalarException
{
  Connection conn         = null;
  PreparedStatement pstmt = null;
  ResultSet rset          = null;
  boolean success         = false;

  ArrayList HoldList   = new ArrayList();

  try
  {
      if ( dbTransaction != null )
      {
          conn    = dbTransaction.getConnection();
      }
      else
      {
          conn    = DataSource.getConnection();
      }

      pstmt   = conn.prepareStatement ( "{ CALL sp_hold_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
      pstmt.setString( 1, fromdate );
      pstmt.setString( 2, todate );
      pstmt.setString( 3, customers );
      pstmt.setString( 4, products );
      pstmt.setString( 5, region );
      pstmt.setString( 6, repoffices );
      pstmt.setString( 7, enduser );
      pstmt.setString( 8, sperson );
      pstmt.setString( 9, defaultPrompt );
      pstmt.setString( 10, bookingType );
      pstmt.setInt( 11, orderType );
      pstmt.setString( 12, endCustomer );
      pstmt.setString( 13, period );
      pstmt.setString( 14, reportType );

      rset    = pstmt.executeQuery();

      HoldList = ExcelUtils.getAllResultSetRows( rset, true );
      success = true;
  }
  catch ( Exception e )
  {
      e.printStackTrace();
      throw new ScalarException ( "Could not load Hold Orders", e );
  }
  finally
  {
      if ( dbTransaction == null )
      {
          if ( conn != null )
          {
              try
              {
                  if ( success )
                  {
                      conn.commit();
                  }
                  else
                  {
                      conn.rollback();
                  }
              }catch ( SQLException e ){}
          }
      }
      if ( rset != null )
      {
          try { rset.close(); }catch ( SQLException ignore ) {}
      }
      if ( pstmt != null )
      {
          try { pstmt.close(); } catch ( SQLException ignore ) {}
      }
      if ( conn != null )
      {
          try { conn.close(); } catch ( SQLException ignore ) {}
      }
  }
  return HoldList;
}
public static String loadSalesHoldReportCSV ( DBTransaction dbTransaction, String fromdate,String todate,String customers,String products,String region,String repoffices,String enduser,String sperson,String defaultPrompt, String bookingType,int orderType,String endCustomer,String period,String reportType) throws ScalarException
{
    Connection conn         = null;
    PreparedStatement pstmt = null;
    ResultSet rset          = null;
    boolean success         = false;
    String csvString        = null;

    try
    {
        if ( dbTransaction != null )
        {
            conn    = dbTransaction.getConnection();
        }
        else
        {
            conn    = DataSource.getConnection();
        }

        pstmt   = conn.prepareStatement ( "{ CALL sp_hold_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,? ) }" );
        pstmt.setString( 1, fromdate );
        pstmt.setString( 2, todate );
        pstmt.setString( 3, customers );
        pstmt.setString( 4, products );
        pstmt.setString( 5, region );
        pstmt.setString( 6, repoffices );
        pstmt.setString( 7, enduser );
        pstmt.setString( 8, sperson );
        pstmt.setString( 9, defaultPrompt );
        pstmt.setString( 10, bookingType );
        pstmt.setInt( 11, orderType );
        pstmt.setString( 12, endCustomer );
        pstmt.setString( 13, period );
        pstmt.setString( 14, reportType );

        rset    = pstmt.executeQuery();
        csvString = ExcelUtils.getCSVString( rset, true );
    }
    catch ( Exception e )
    {
       e.printStackTrace();
       throw new ScalarException ( "Could not load Hold Orders", e );
    }
    finally
    {
       if ( rset != null )
       {
           try { rset.close(); }catch ( SQLException ignore ) {}
       }
       if ( pstmt != null )
       {
           try { pstmt.close(); } catch ( SQLException ignore ) {}
       }
       if ( conn != null )
       {
           try { conn.close(); } catch ( SQLException ignore ) {}
       }
    }
    return csvString;
}


 public static ArrayList loadShipmentReprot ( DBTransaction dbTransaction, String product,String salesorderno,String customer,String datefrom,String dateto,int orderType,String period,String reportType,String defaultPrompt) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList ShipmentList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_shipment_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         pstmt.setString( 1, product );
         pstmt.setString( 2, salesorderno );
         pstmt.setString( 3, customer );
         pstmt.setString( 4, datefrom );
         pstmt.setString( 5, dateto );
         pstmt.setInt( 6, orderType );
         pstmt.setString( 7, period );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );

         rset    = pstmt.executeQuery();

         ShipmentList = ExcelUtils.getAllResultSetRows(rset, true);

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Shipment records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return ShipmentList;
 }
 public static String loadShipmentReportCSV ( DBTransaction dbTransaction, String product,String salesorderno,String customer,String datefrom,String dateto,int orderType,String period,String reportType,String defaultPrompt) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     String csvString        = null;

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_shipment_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         pstmt.setString( 1, product );
         pstmt.setString( 2, salesorderno );
         pstmt.setString( 3, customer );
         pstmt.setString( 4, datefrom );
         pstmt.setString( 5, dateto );
         pstmt.setInt( 6, orderType );
         pstmt.setString( 7, period );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );

         rset    = pstmt.executeQuery();

         csvString = ExcelUtils.getCSVString( rset, true );
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Shipment records", e );
     }
     finally
     {
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return csvString;
 }
 /*
      This method is used as input for the xl report Invoice Report
  */
 public static ArrayList getInvoiceDetails ( DBTransaction dbTransaction, String customer,String productId,String soNumber,String fromDate,String toDate,String reportType, String category, String invoiceNo, String defaultPrompt) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList ShipmentList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_revenue_shipment_report( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         pstmt.setString( 1, customer );
         pstmt.setString( 2, productId );
         pstmt.setString( 3, soNumber );
         pstmt.setString( 4, fromDate );
         pstmt.setString( 5, toDate );
         pstmt.setString( 6, category );
         pstmt.setString( 7, invoiceNo );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );

         rset    = pstmt.executeQuery();

         ShipmentList = ExcelUtils.getAllResultSetRows(rset, true);

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Shipment records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return ShipmentList;
 }
public static String getInvoiceDetailsCSV ( DBTransaction dbTransaction, String customer,String productId,String soNumber,String fromDate,String toDate,String reportType, String category, String invoiceNo, String defaultPrompt) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     String csvString        = null;


     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_revenue_shipment_report( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         pstmt.setString( 1, customer );
         pstmt.setString( 2, productId );
         pstmt.setString( 3, soNumber );
         pstmt.setString( 4, fromDate );
         pstmt.setString( 5, toDate );
         pstmt.setString( 6, category );
         pstmt.setString( 7, invoiceNo );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );

         rset    = pstmt.executeQuery();
         csvString = ExcelUtils.getCSVString( rset, true );
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Shipment records", e );
     }
     finally
     {
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return csvString;
 }

 /*
      This method is used as input for the xl report Billing(Shipped) Report
  */
public static ArrayList loadBillingReprot ( DBTransaction dbTransaction, String product,String salesorderno,String customer,String salesPerson,String datefrom,String dateto,int orderType,String period,String reportType,String region,String defaultPrompt, String endUser) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList ShipmentList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_billing_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? ) }" );
         pstmt.setString( 1, product );
         pstmt.setString( 2, salesorderno );
         pstmt.setString( 3, customer );
         pstmt.setString( 4, salesPerson );
         pstmt.setString( 5, datefrom );
         pstmt.setString( 6, dateto );
         pstmt.setInt( 7, orderType );
         pstmt.setString( 8, period );
         pstmt.setString( 9, reportType );
         pstmt.setString( 10, defaultPrompt );
         pstmt.setString( 11, region );
         pstmt.setString( 12, endUser );


         rset    = pstmt.executeQuery();

         ShipmentList = ExcelUtils.getAllResultSetRows(rset, true );

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Shipment records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return ShipmentList;
 }

 public static ArrayList loadBBBReprotList ( DBTransaction dbTransaction, Map paramMap) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList ShipmentList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_BBB_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }" );
         pstmt.setString( 1, (String)paramMap.get("fromDate") );
         pstmt.setString( 2, (String)paramMap.get("toDate") );
         pstmt.setString( 3, (String)paramMap.get("customerId") );
         pstmt.setString( 4, (String)paramMap.get("productId") );
         pstmt.setString( 5, (String)paramMap.get("region") );
         pstmt.setString( 6, (String)paramMap.get("regionRep") );
         pstmt.setString( 7, (String)paramMap.get("endCustomer") );
         pstmt.setString( 8, (String)paramMap.get("salesPerson") );
         pstmt.setString( 9, (String)paramMap.get("orderType") );
         pstmt.setString( 10, (String)paramMap.get("endUser") );
         pstmt.setString( 11, (String)paramMap.get("reportType") );
         
         rset    = pstmt.executeQuery();

         ShipmentList = ExcelUtils.getAllResultSetRows(rset, true );

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Shipment records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return ShipmentList;
 }
   /*
      This method is used as input for the xl report Vendor Shipment Report
  */
public static ArrayList loadVendorShipmentReprot ( DBTransaction dbTransaction, String customerIds,String vendorId,String endCustomer,String soNubmer,String productId,int orderTypeInt,String fromDate,String toDate,String reportType,String defaultPrompt) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList VendorShipmentList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_vendor_shipment_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         pstmt.setString( 1, customerIds );
         pstmt.setString( 2, vendorId );
         pstmt.setString( 3, endCustomer );
         pstmt.setString( 4, soNubmer );
         pstmt.setString( 5, productId );
         pstmt.setInt   ( 6, orderTypeInt );
         pstmt.setString( 7, fromDate );
         pstmt.setString( 8, toDate );
         pstmt.setString( 9, reportType );
         pstmt.setString( 10, defaultPrompt );

         rset    = pstmt.executeQuery();

         VendorShipmentList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Vendor Shipment records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return VendorShipmentList;
 }

  /*
      This method is used as input for the xl report Pending Shipment Report
  */
public static ArrayList loadPendingShipmentReprot ( DBTransaction dbTransaction,String customer,String product,String salesorderno,int orderTypeInt,String dateRange,String reportType,String vendor,String defaultPrompt) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList PendingShipmentList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_pending_shipment_report( ?, ?, ?, ?, ?, ?, ?,? ) }" );
         pstmt.setString( 1, customer );
         pstmt.setString( 2, product );
         pstmt.setString( 3, salesorderno );
         pstmt.setInt( 4, orderTypeInt );
         pstmt.setString( 5, dateRange );
         pstmt.setString( 6, reportType );
         pstmt.setString( 7, vendor );
         pstmt.setString( 8, defaultPrompt );

         rset    = pstmt.executeQuery();

         PendingShipmentList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Pending Shipment records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return PendingShipmentList;
 }

 /*
      This method is used as input for the xl report Performance Report
  */
public static ArrayList loadPerformanceReport ( DBTransaction dbTransaction, Map paramsMap ) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList PerformanceList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_performance_report_proc( ?, ?, ?, ?, ?, ?, ? ) }" );
         pstmt.setString( 1, (String)paramsMap.get( "fromDate" ) );
         pstmt.setString( 2, (String)paramsMap.get( "toDate" ) );
         pstmt.setString( 3, (String)paramsMap.get( "productIds" ) );
         pstmt.setString( 4, (String)paramsMap.get( "customerId" ) );
         pstmt.setInt( 5, Integer.parseInt ( (String)paramsMap.get( "orderType" ) ) );
         pstmt.setString( 6, (String)paramsMap.get( "reportType" ) );
         pstmt.setString( 7, (String)paramsMap.get( "defaultPrompt" ) );

         rset    = pstmt.executeQuery();

         PerformanceList = ExcelUtils.getAllResultSetRows(rset, true );
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Performance records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return PerformanceList;
 }
    public static Workbook loadPerformanceReportWorkbook( DBTransaction dbTransaction, Map paramsMap ) throws ScalarException, IOException
    {
         ArrayList reportList = NewCustomerDAO.loadPerformanceReport ( dbTransaction, paramsMap );
         return DataDumpReportsHelper.getWorkbook( reportList, "Performance Report", (String)paramsMap.get("fromDate"), (String)paramsMap.get("toDate"), null  );
    }
     /**
      *
      * @param salesOrderType
      * 0 - sample sales orders
      * 1 - Regular Sales Orders
      * @param isSalesOrderTypeConsidered
      * true  - only the salesOrderType end Users are included
      * false - both SO end Users are included (salesOrderType is ignored)
      * @return
      * @throws ScalarException
      */
     public static ArrayList<LabelValueBean> loadAllEndUsersByUserType(DBTransaction dbTransaction,int salesOrderType, boolean isSalesOrderTypeConsidered,User user) throws ScalarException
        {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            ArrayList<LabelValueBean> allEndUsers = new ArrayList<LabelValueBean> ();

            try
            {
                if ( dbTransaction != null )
                {
                    conn = dbTransaction.getConnection();
                }
                else
                {
                    conn = DataSource.getConnection();
                }

                if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
                {
                    if ( isSalesOrderTypeConsidered )
                    {
                        pstmt = conn.prepareStatement ( "(SELECT  DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ENDCUSTOMER_RELATIONSHIP CER WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CER.ENDCUSTOMER_ID AND CER.PARENTCUSTOMER_ID = ? )\n" +
                                "UNION \n" +
                                "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ENDCUSTOMER_ID,ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID)) AND C.CUSTOMER_ID = ? AND SO.SO_IS_SAMPLE = ? ) ORDER BY C_NAME " );
                        pstmt.setInt ( 1, user.getCustomerId() );
                        pstmt.setInt ( 2, user.getCustomerId() );
                        pstmt.setInt ( 3, salesOrderType );
                    }
                    else
                    {
                        pstmt = conn.prepareStatement ( "(SELECT  DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ENDCUSTOMER_RELATIONSHIP CER WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CER.ENDCUSTOMER_ID AND CER.PARENTCUSTOMER_ID = ? )\n" +
                                "UNION \n" +
                                "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ENDCUSTOMER_ID,ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID)) AND C.CUSTOMER_ID = ? ) ORDER BY C_NAME " );
                         pstmt.setInt ( 1, user.getCustomerId() );
                         pstmt.setInt ( 2, user.getCustomerId() );
                    }
                }
                else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
                {
                     if ( isSalesOrderTypeConsidered )
                    {
                        pstmt = conn.prepareStatement ( "(SELECT  DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ENDCUSTOMER_RELATIONSHIP CER WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CER.ENDCUSTOMER_ID AND CER.PARENTCUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) )\n" +
                                "UNION \n" +
                                "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ENDCUSTOMER_ID,ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID)) AND C.CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) AND SO.SO_IS_SAMPLE = ? ) ORDER BY C_NAME " );
                        pstmt.setInt ( 1, user.getSalesPersonId() );
                        pstmt.setInt ( 2, user.getSalesPersonId() );
                        pstmt.setInt ( 3, salesOrderType );
                    }
                    else
                    {
                        pstmt = conn.prepareStatement ( "(SELECT  DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ENDCUSTOMER_RELATIONSHIP CER WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CER.ENDCUSTOMER_ID AND CER.PARENTCUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) )\n" +
                                "UNION \n" +
                                "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ENDCUSTOMER_ID,ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID)) AND C.CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) ) ORDER BY C_NAME " );
                         pstmt.setInt ( 1, user.getSalesPersonId() );
                         pstmt.setInt ( 2, user.getSalesPersonId() );
                    }
                }
                else
                {
                    if ( isSalesOrderTypeConsidered )
                    {
                        pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 3) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL AND SO.SO_Is_Sample = ?) ORDER BY C_NAME" );
                        pstmt.setInt ( 1, salesOrderType );
                    }
                    else
                    {
                        pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 3) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL) ORDER BY C_NAME" );
                    }
                }
                rs = pstmt.executeQuery ();
                while ( rs.next () )
                {
                    String endUser_name = rs.getString ( "C_Name" );
                    allEndUsers.add ( new org.apache.struts.util.LabelValueBean ( endUser_name, endUser_name ) );
                }
            }
            catch ( Exception sqlex )
            {
                sqlex.printStackTrace ();
                throw new ScalarException ( "Could not get End Users from database", sqlex );
            }
            finally
            {
                if ( rs != null )
                {
                    try
                    {
                        rs.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
            }
            return allEndUsers;
        }

    /**
      *
      * @param salesOrderType
      * 0 - sample sales orders
      * 1 - Regular Sales Orders
      * @param isSalesOrderTypeConsidered
      * true  - only the salesOrderType end Customers are included
      * false - both SO end customers are included (salesOrderType is ignored)
      * @return
      * @throws ScalarException
      */
    public static ArrayList loadAllEndCustomersWithSOEndcustomersByUserType( DBTransaction dbTransaction, int salesOrderType, boolean isSalesOrderTypeConsidered,User user ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList allEndCustomers = new ArrayList ();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
            {
                if ( isSalesOrderTypeConsidered )
                {
                    pstmt = conn.prepareStatement ( "( SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ODM_RELATIONSHIP COR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = COR.ODMCUSTOMER_ID AND COR.PARENTCUSTOMER_ID = ? )\n" +
                            "UNION \n" +
                            "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL AND C.CUSTOMER_ID = ? AND SO.SO_Is_Sample = ? ) ORDER BY C_NAME" );
                    pstmt.setInt( 1, user.getCustomerId() );
                    pstmt.setInt( 2, user.getCustomerId() );
                    pstmt.setInt( 3, salesOrderType );
                }
                else
                {
                    pstmt = conn.prepareStatement ( "( SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ODM_RELATIONSHIP COR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = COR.ODMCUSTOMER_ID AND COR.PARENTCUSTOMER_ID = ? )\n" +
                            "UNION \n" +
                            "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL AND C.CUSTOMER_ID = ? ) ORDER BY C_NAME" );
                    pstmt.setInt( 1, user.getCustomerId() );
                    pstmt.setInt( 2, user.getCustomerId() );
                }
            }
            else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                if ( isSalesOrderTypeConsidered )
                {
                    pstmt = conn.prepareStatement ( "( SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ODM_RELATIONSHIP COR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = COR.ODMCUSTOMER_ID AND COR.PARENTCUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) )\n" +
                            "UNION \n" +
                            "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL AND C.CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) AND SO.SO_Is_Sample = ? ) ORDER BY C_NAME" );
                    pstmt.setInt( 1, user.getSalesPersonId() );
                    pstmt.setInt( 2, user.getSalesPersonId() );
                    pstmt.setInt( 3, salesOrderType );
                }
                else
                {
                    pstmt = conn.prepareStatement ( "( SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_ODM_RELATIONSHIP COR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = COR.ODMCUSTOMER_ID AND COR.PARENTCUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) )\n" +
                            "UNION \n" +
                            "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_ENDCUSTOMER_ID IS NULL AND C.CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = ? ) ) ORDER BY C_NAME" );
                    pstmt.setInt( 1, user.getSalesPersonId() );
                    pstmt.setInt( 2, user.getSalesPersonId() );
                }
            }
            else
            {
                if ( isSalesOrderTypeConsidered )
                {
                    pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 2) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) AND SO.SO_Is_Sample = ?) ORDER BY C_NAME" );
                    pstmt.setInt( 1, salesOrderType );
                }
                else
                {
                    pstmt = conn.prepareStatement ( "(SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND CTR.C_TYPE_ID = 2) UNION (SELECT DISTINCT C_NAME,C.CUSTOMER_ID FROM CUSTOMER C, SALES_ORDER SO WHERE C_DISABLED = 1 AND C.CUSTOMER_ID = ISNULL(SO.SO_ODM_ID,SO.CUSTOMER_ID) ) ORDER BY C_NAME" );
                }
            }

            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                String endCustomerName = rs.getString ( "C_Name" );
                allEndCustomers.add ( new org.apache.struts.util.LabelValueBean ( endCustomerName, endCustomerName ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End Customers from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return allEndCustomers;
    }


     public static ArrayList<LabelValueBean> getAllCustomerNamesByUserTypeBySetup( DBTransaction dbTransaction, User user, String searchCustomerName ) throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;


         ArrayList<LabelValueBean> customers = new ArrayList<LabelValueBean>();
         searchCustomerName = (searchCustomerName == null) ? "" : searchCustomerName.trim();
         try
         {
             if ( dbTransaction != null )
             {
                 conn = dbTransaction.getConnection();
             }
             else
             {
                 conn = DataSource.getConnection();
             }
             if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
             {
                 pstmt = conn.prepareStatement ( "select Customer_ID,C_Name from Customer where C_Disabled = 1 and customer_id = ? and is_sample=1" );
                 pstmt.setInt ( 1, user.getCustomerId() );
             }
             else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
             {
                 String query = "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')";
                 if ( ConfigManager.isLoadEndCustomersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')";
                 }
                 if ( ConfigManager.isLoadEndUsersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')";
                 }
                 pstmt = conn.prepareStatement ( query + " ORDER BY C_Name" );
                 customers.add(new LabelValueBean("All Customers","0"));
             }
             else
             {
                 String query = "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C.C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 ";
                 if ( ConfigManager.isLoadEndCustomersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C.C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 ";
                 }
                 if ( ConfigManager.isLoadEndUsersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C.C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 ";
                 }
                 pstmt = conn.prepareStatement( query + " ORDER BY C_Name" );
                 customers.add(new LabelValueBean("All Customers","0"));
             }
             rs = pstmt.executeQuery();
             while(rs.next())
             {
                 int customerId = rs.getInt("Customer_ID");
                 customers.add(new LabelValueBean(rs.getString("C_Name"),Integer.toString(customerId)));
             }

         }
         catch (Exception sqlex)
         {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get Customers from database", sqlex);
         }
         finally
         {
             if (rs != null)
             {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }
         return customers;
    }
    public static SalesPerson getSalesPersonDetails(DBTransaction dbtransaction, String salesPersonName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SalesPerson salesPerson = null;
        int addressId = -1;
        try {
              if ( dbtransaction != null )
              {
                  conn = dbtransaction.getConnection();
              }
              else
              {
                  conn = DataSource.getConnection();
              }
              pstmt = conn.prepareStatement( "select SalesPerson_Id, SalesPerson_Name, Sales_Percent, Address_Id, Region_Id, SALES_ACK_MAIL, QUOTE_ACK_MAIL, SHIP_ALERT_MAIL from sales_Persons where SalesPerson_Name = ?" );
              pstmt.setString ( 1, salesPersonName );
              rs = pstmt.executeQuery();

              if(rs.next())
              {
                    salesPerson = new SalesPerson();
                    salesPerson.setSalesPersonId(rs.getInt("SalesPerson_Id") );
                    salesPerson.setSalesPerson  (rs.getString("SalesPerson_Name"));
                    salesPerson.setSalesPercent (rs.getFloat("Sales_Percent") );
                    salesPerson.setRegionId       (rs.getInt("Region_Id"));
                    addressId = rs.getInt("Address_Id");
                    salesPerson.setSalesAckMail( rs.getInt ( "SALES_ACK_MAIL" ) == 0 );
                    salesPerson.setQuoteAckMail( rs.getInt ( "QUOTE_ACK_MAIL" ) == 0 );
                    salesPerson.setShipAlerts( rs.getInt ( "SHIP_ALERT_MAIL" ) == 0 );
              }
        }
        catch (Exception sqlex) {
                sqlex.printStackTrace();
                throw new ScalarException ("Could not get SalesPersons from database", sqlex);
        }
        finally {
                if (rs != null) {
                    try { rs.close(); rs = null; } catch (SQLException excp) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); pstmt = null; } catch (SQLException excp) {}
                }
                if (conn != null) {
                    try { conn.close(); conn = null; } catch (SQLException excp) {}
                }
        }
        if ( addressId > 0 )
        {
            //Address address = CustomerDAO.getAddress( addressId );
            Address address = NewCustomerDAO.loadAddressFromCT( addressId );
            if ( address != null )
            {
                address.setAddressId( addressId );
            }
            salesPerson.setAddress( address );
        }
        return salesPerson;
     }
    public static ArrayList<LabelValueBean> loadAllShippingMethods( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String method = null;
        String account = null;
        int shippingMethodId;
        ArrayList<LabelValueBean> shippingList = new ArrayList<LabelValueBean>();
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("select Shipping_method_id, Shipping_Method, Shipping_Account from Shipping_Methods_Table NOLOCK where sm_customer_id= ?");
            pstmt.setInt(1,customerId);
            rs = pstmt.executeQuery();

            while(rs.next())
            {
                shippingMethodId = rs.getInt ( "Shipping_method_id" );
                method = rs.getString("Shipping_Method");
                account = rs.getString("Shipping_Account");
                shippingList.add ( new org.apache.struts.util.LabelValueBean ( method+"   #   "+account, shippingMethodId+"" ) );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Shipping methods from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return shippingList;
    }

    public static ArrayList<LabelValueBean> getAllDistributorNamesByUserType( DBTransaction dbTransaction, User user, boolean needToShowFirstOption ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        int customerId          = 0;

        ArrayList<LabelValueBean> customers     = new ArrayList<LabelValueBean>();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
            {
                pstmt = conn.prepareStatement ( "select Customer_ID,C_Name from Customer where C_Disabled = 1 and C_IS_Distributor = 1 and customer_id = ?" );
                pstmt.setInt ( 1, user.getCustomerId() );
            }
            else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                pstmt = conn.prepareStatement ( "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR WHERE C_DISABLED = 1 AND C_IS_DISTRIBUTOR = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND CSR.SALESPERSON_ID = ? AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ORDER BY C_Name" );
                pstmt.setInt ( 1, user.getSalesPersonId() );
                if( needToShowFirstOption )
                {
                    customers.add( new LabelValueBean( "All Distributor","0" ) );
                }
            }
            else
            {
                pstmt = conn.prepareStatement("select Customer_ID, C_Name from Customer where C_Disabled = 1  and C_IS_Distributor = 1 ORDER BY C_Name");
                if( needToShowFirstOption )
                {
                    customers.add( new LabelValueBean( "All Distributor", "0" ) );
                }
            }

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                customerId = rs.getInt( "Customer_ID" );
                customers.add( new LabelValueBean( rs.getString( "C_Name" ), Integer.toString( customerId ) ) );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Distributors from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
    }
    public static ArrayList loadNewEndUsersReprotByCustomer ( DBTransaction dbTransaction) throws ScalarException
    {
      Connection conn         = null;
      PreparedStatement pstmt = null;
      ResultSet rset          = null;
      boolean success         = false;

      ArrayList newEndUsersList   = new ArrayList();

      try
      {
          if ( dbTransaction != null )
          {
              conn    = dbTransaction.getConnection();
          }
          else
          {
              conn    = DataSource.getConnection();
          }

          pstmt   = conn.prepareStatement ( "{ CALL SP_NEW_END_USER_REPORT_PROC() }" );
          rset    = pstmt.executeQuery();

          newEndUsersList = ExcelUtils.getAllResultSetRows(rset, true );
          success = true;
      }
      catch ( Exception e )
      {
          e.printStackTrace();
          throw new ScalarException ( "Could not load Performance records", e );
      }
      finally
      {
          if ( dbTransaction == null )
          {
              if ( conn != null )
              {
                  try
                  {
                      if ( success )
                      {
                          conn.commit();
                      }
                      else
                      {
                          conn.rollback();
                      }
                  }catch ( SQLException e ){}
              }
          }
          if ( rset != null )
          {
              try { rset.close(); }catch ( SQLException ignore ) {}
          }
          if ( pstmt != null )
          {
              try { pstmt.close(); } catch ( SQLException ignore ) {}
          }
          if ( conn != null )
          {
              try { conn.close(); } catch ( SQLException ignore ) {}
          }
      }
      return newEndUsersList;
    }
    public static ArrayList loadnewEndCustomersReprotByCustomer ( DBTransaction dbTransaction) throws ScalarException
    {
      Connection conn         = null;
      PreparedStatement pstmt = null;
      ResultSet rset          = null;
      boolean success         = false;

      ArrayList newEndCustomersList   = new ArrayList();

      try
      {
          if ( dbTransaction != null )
          {
              conn    = dbTransaction.getConnection();
          }
          else
          {
              conn    = DataSource.getConnection();
          }

          pstmt   = conn.prepareStatement ( "{ CALL SP_NEW_END_CUSTOMER_REPORT_PROC() }" );
          rset    = pstmt.executeQuery();

          newEndCustomersList = ExcelUtils.getAllResultSetRows(rset, true );

          success = true;
      }
      catch ( Exception e )
      {
          e.printStackTrace();
          throw new ScalarException ( "Could not load New End Customers records", e );
      }
      finally
      {
          if ( dbTransaction == null )
          {
              if ( conn != null )
              {
                  try
                  {
                      if ( success )
                      {
                          conn.commit();
                      }
                      else
                      {
                          conn.rollback();
                      }
                  }catch ( SQLException e ){}
              }
          }
          if ( rset != null )
          {
              try { rset.close(); }catch ( SQLException ignore ) {}
          }
          if ( pstmt != null )
          {
              try { pstmt.close(); } catch ( SQLException ignore ) {}
          }
          if ( conn != null )
          {
              try { conn.close(); } catch ( SQLException ignore ) {}
          }
      }
      return newEndCustomersList;
    }
    public static ArrayList loadNewEndUserEndCustomersReprotByCustomer ( DBTransaction dbTransaction) throws ScalarException
    {
      Connection conn         = null;
      CallableStatement cstmt = null;
      ResultSet rset          = null;

      ArrayList newEndUserEndCustomersList   = new ArrayList();

      try
      {
          if ( dbTransaction != null )
          {
              conn    = dbTransaction.getConnection();
          }
          else
          {
              conn    = DataSource.getConnection();
          }

          cstmt   = conn.prepareCall ( "{ CALL SP_NEW_END_USER_END_CUST_REPORT_PROC() }" );
          rset    = cstmt.executeQuery();

          newEndUserEndCustomersList = ExcelUtils.getAllResultSetRows(rset, true );
      }
      catch ( Exception e )
      {
          e.printStackTrace();
          throw new ScalarException ( "Could not load New End Users, End Customers records", e );
      }
      finally
      {
          if ( rset != null )
          {
              try { rset.close(); }catch ( SQLException ignore ) {}
          }
          if ( cstmt != null )
          {
              try { cstmt.close(); } catch ( SQLException ignore ) {}
          }
          if ( conn != null )
          {
              try { conn.close(); } catch ( SQLException ignore ) {}
          }
      }
      return newEndUserEndCustomersList;
    }
    public static ArrayList<LabelValueBean> getAllCustomerNamesByUserTypeBySetup( DBTransaction dbTransaction, User user, String searchCustomerName, int isSample) throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;

         ArrayList<LabelValueBean> customers = new ArrayList<LabelValueBean>();
         searchCustomerName = (searchCustomerName == null) ? "" : searchCustomerName.trim();
         try
         {
             if ( dbTransaction != null )
             {
                 conn = dbTransaction.getConnection();
             }
             else
             {
                 conn = DataSource.getConnection();
             }
             if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
             {
                 pstmt = conn.prepareStatement ( "select Customer_ID,C_Name from Customer where C_Disabled = 1 and customer_id = ? and is_sample=1" );
                 pstmt.setInt ( 1, user.getCustomerId() );
             }
             else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
             {
                 String query = "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')";
                 if ( ConfigManager.isLoadEndCustomersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')";
                 }
                 if ( ConfigManager.isLoadEndUsersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')";
                 }
                 pstmt = conn.prepareStatement ( query + " ORDER BY C_Name" );
                 customers.add(new LabelValueBean("All Customers","0"));
             }
             else
             {
                 String query;
                 if(isSample == 1)
                 {
                     query= "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C.C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 and is_sample=1 ";
                 }
                 else
                 {
                    query= "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C.C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 ";
                 }
                 if ( ConfigManager.isLoadEndCustomersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C.C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 ";
                 }
                 if ( ConfigManager.isLoadEndUsersForNewSales() )
                 {
                     query += " UNION SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR WHERE C.C_NAME LIKE '" + StringUtils.formatToHandleQuote(searchCustomerName) + "%' AND C.C_DISABLED = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 ";
                 }
                 pstmt = conn.prepareStatement( query + " ORDER BY C_Name" );
                 customers.add(new LabelValueBean("All Customers","0"));
             }
             rs = pstmt.executeQuery();
             while(rs.next())
             {
                 int customerId = rs.getInt("Customer_ID");
                 customers.add(new LabelValueBean(rs.getString("C_Name"),Integer.toString(customerId)));
             }

         }
         catch (Exception sqlex)
         {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get Customers from database", sqlex);
         }
         finally
         {
             if (rs != null)
             {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }
         return customers;
    }
    public static ArrayList<ShippingMethod> getCustomerShippingMethodList ( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;

        ArrayList<ShippingMethod> shippingMethodList   = new ArrayList<ShippingMethod>();
        try
        {
            if (dbTransaction != null)
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT SHIPPING_METHOD_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT, IS_DISABLED FROM SHIPPING_METHODS_TABLE WHERE SM_CUSTOMER_ID=?");
            pstmt.setInt ( 1, customerId );
            rset = pstmt.executeQuery();
            while (rset.next())
            {
                ShippingMethod shippingMethod = new ShippingMethod();
                shippingMethod.setShippingMethodId( rset.getInt ( "SHIPPING_METHOD_ID" ) );
                shippingMethod.setShippingmethod( rset.getString ( "SHIPPING_METHOD" ) );
                shippingMethod.setShippingacc( rset.getString ( "SHIPPING_ACCOUNT" ) );
                shippingMethod.setDisabled( rset.getInt ( "IS_DISABLED" ) == 0 );
                shippingMethod.setCustomerId( customerId );

                shippingMethodList.add(shippingMethod);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Could not load Customer Shipping Methods", e);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) { }
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) { }
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) { }
            }
        }
        return shippingMethodList;
    }
    public static void updateShippingMethod (DBTransaction dbTransaction, int shippingMethodId, String methodname, String account) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if (dbTransaction != null)
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            if( methodname == null )
            {
                methodname = "";
            }
            if( account == null )
            {
                account = "";
            }

            pstmt = conn.prepareStatement ( "UPDATE SHIPPING_METHODS_TABLE SET SHIPPING_METHOD = ?, SHIPPING_ACCOUNT = ? WHERE SHIPPING_METHOD_ID = ?");

            pstmt.setString ( 1, methodname.trim() );
            pstmt.setString ( 2, account.trim());
            pstmt.setInt ( 3, shippingMethodId );
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update Shipping Method Name, \"" + methodname + "\" into database", sqle);
        }
        finally
        {
            if( dbTransaction == null && !success && conn != null )
            {
                try
                { conn.rollback(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    public static ShippingMethod getShippingMethod ( DBTransaction dbTransaction, int shippingMethodId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        ShippingMethod shippingMethod = null;
        try
        {
            if (dbTransaction != null)
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT, IS_DISABLED FROM SHIPPING_METHODS_TABLE WHERE SHIPPING_METHOD_ID=?");
            pstmt.setInt ( 1, shippingMethodId );
            rset = pstmt.executeQuery();
            if (rset.next())
            {
                shippingMethod = new ShippingMethod();
                shippingMethod.setCustomerId( rset.getInt ( "SM_CUSTOMER_ID" ) );
                shippingMethod.setShippingmethod( rset.getString ( "SHIPPING_METHOD" ) );
                shippingMethod.setShippingacc( rset.getString ( "SHIPPING_ACCOUNT" ) );
                shippingMethod.setDisabled( rset.getInt ( "IS_DISABLED" ) == 0 );
                shippingMethod.setShippingMethodId( shippingMethodId );
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Could not load Shipping Method", e);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) { }
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) { }
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) { }
            }
        }
        return shippingMethod;
    }
    public static void updateShippingMethodStatus (DBTransaction dbTransaction, int shippingMethodId, boolean disable ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if (dbTransaction != null)
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

           pstmt = conn.prepareStatement ( "UPDATE SHIPPING_METHODS_TABLE SET IS_DISABLED = ? WHERE SHIPPING_METHOD_ID = ?");

            pstmt.setInt ( 1, (disable?0:1) );
            pstmt.setInt ( 2, shippingMethodId );
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update Shipping Method Status", sqle);
        }
        finally
        {
            if( dbTransaction == null && !success && conn != null )
            {
                try
                { conn.rollback(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

}